package com.example.fp.cgc.mapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper
import com.example.fp.cgc.entity.*
import org.apache.ibatis.annotations.Select

interface AppsMapper : BaseMapper<Apps> {

    /**
     * 获取应用表数据
     */
    @Select("select " +
            "id," +
            "title," +
            "volume," +
            "downloads," +
            "follow," +
            "comment," +
            "tags," +
            "rank_num as num," +
            "rom," +
            "developer," +
            "rank_num_users as usernum," +
            "update_time as time" +
            " from apps")
    fun selectAppsList(): List<Apps>

    /**
     * 获取开发者信息
     */
    @Select("SELECT" +
            "       count(*) as appnum," +
            "       developer as name," +
            "       ROUND(AVG(rank_num),1) as score," +
            "       ROUND(sum(rank_num_users),1) as scorenum," +
            "       ROUND(sum(downloads),1) as downloads," +
            "       ROUND(sum(follow),1) as follow " +
            "FROM apps " +
            "group by developer order by appnum desc limit 15")
    fun getDevelopList(): List<DeveloperData>

    /**
     * 获取应用标签
     */
    @Select("select tags from apps")
    fun getAppsTags(): List<String>

    /**
     * 按条件获取应用列表
     */
    @Select("<script>" +
            "select " +
            "id," +
            "title," +
            "volume," +
            "downloads," +
            "follow," +
            "comment," +
            "tags," +
            "rank_num as num," +
            "rom," +
            "developer," +
            "rank_num_users as usernum," +
            "update_time as time" +
            " from apps" +
            "            <if test=\"start!=null and size!=null\">\n" +
            "                limit #{start},#{size}\n" +
            "            </if>\n" +
            "</script>")
    fun selectAppsPage(pageCondition: PageCondition): List<Apps>

    /**
     * 获取应用搜索列表
     */
    @Select("<script>" +
            "select " +
            "id," +
            "title," +
            "volume," +
            "downloads," +
            "follow," +
            "comment," +
            "tags," +
            "rank_num as num," +
            "rom," +
            "developer," +
            "rank_num_users as usernum," +
            "update_time as time" +
            " from apps" +
            "<where>" +
            "<if test=\"title != null and title != ''\">" +
            "title LIKE CONCAT('%',#{title},'%')" +
            "</if>" +
            "</where>" +
            "<if test=\"start!=null and size!=null\">" +
            "limit #{start},#{size}" +
            "</if>" +
            "</script>")
    fun selectSearchAppsPage(pageCondition: PageCondition): List<Apps>

    /**
     * 获取应用数量
     */
    @Select("select count(*) from apps")
    fun selectAppsCount(): Int

    /**
     * 获取应用
     */
    @Select("select t.rn from (\n" +
            "    select *,row_number()\n" +
            "        over (order by id) rn from apps) t\n" +
            " where t.id = #{id}")
    fun getAppIndex(id: Int): Int

    /**
     * 获取搜索应用
     */
    @Select(
            "<script>" +
                    "select t.rn from (\n" +
                    "    select *,row_number()\n" +
                    "        over (order by id) rn from apps" +
                    "         <where>" +
                    "            <if test=\"title != null and title != ''\">" +
                    "title LIKE CONCAT('%',#{title},'%')" +
                    "            </if>" +
                    "         </where>" +
                    ") t\n" +
                    " where t.id = #{id}" +
                    "</script>")
    fun getSearchAppIndex(id: Int, title: String): Int

    /**
     * @author 蔡
     * @since 2020-01-19
     * 模糊搜索
     */
    @Select("<script>" +
            "select count(*) from apps" +
            "         <where>" +
            "            <if test=\"title != null and title != ''\">" +
            "title LIKE CONCAT('%',#{title},'%')" +
            "            </if>" +
            "         </where>" +
            "</script>")
    fun getAppsByName(title: String): Int
}